CREATE TABLE TIPO_ESTACAO (
  TPE_CODIGO CHAR(6) NOT NULL,
  TPE_DESCRICAO VARCHAR(30),
  PRIMARY KEY(TPE_CODIGO)
);

CREATE TABLE ESTACAO (
  EST_CODIGO CHAR(6) NOT NULL,
  EST_TPE_CODIGO CHAR(6) NOT NULL,
  EST_DESCRICAO VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY(EST_CODIGO),
  FOREIGN KEY(EST_TPE_CODIGO) REFERENCES TIPO_ESTACAO
);

CREATE TABLE GRUPO_USUARIO (
  GRP_CODIGO CHAR(6) NOT NULL,
  GRP_DESCRICAO VARCHAR(30) DEFAULT NULL,
  GRP_STATUS CHAR(1) DEFAULT NULL,
  PRIMARY KEY(GRP_CODIGO)
);

CREATE TABLE USUARIO (
  USU_LOGIN CHAR(6) NOT NULL,
  USU_GRP_CODIGO CHAR(6) NOT NULL,
  USU_SENHA CHAR(6) NOT NULL,
  USU_NOME VARCHAR(30) DEFAULT NULL,
  USU_EMAIL CHAR(20) DEFAULT NULL,
  PRIMARY KEY(USU_LOGIN),
  FOREIGN KEY(USU_GRP_CODIGO) REFERENCES GRUPO_USUARIO
);

CREATE TABLE TIPO_PRODUTO (
  TPP_CODIGO CHAR(6) NOT NULL,
  TPP_DESCRICAO VARCHAR(20) DEFAULT NULL,
  PRIMARY KEY(TPP_CODIGO)
);

CREATE TABLE PRODUTO (
  PDT_CODIGO CHAR(6) NOT NULL,
  PDT_TPP_CODIGO CHAR(6) NOT NULL,
  PDT_DESCRICAO VARCHAR(30) NULL,
  PDT_QTDEMB INTEGER DEFAULT  NULL,
  PDT_STATUS CHAR(1) DEFAULT NULL,
  PRIMARY KEY(PDT_CODIGO),
  FOREIGN KEY(PDT_TPP_CODIGO) REFERENCES TIPO_PRODUTO
);

CREATE TABLE ORDEM_PRODUCAO (
  ORP_NUMERO INTEGER NOT NULL ,
  ORP_PDT_CODIGO CHAR(6) NOT NULL,
  ORP_QTDPROD INTEGER DEFAULT NULL,
  PRIMARY KEY(ORP_NUMERO),
  FOREIGN KEY(ORP_PDT_CODIGO) REFERENCES PRODUTO
);

CREATE TABLE NUM_SERIE (
  NUS_NUMERO VARCHAR(30) NOT NULL,
  NUS_ORP_NUMERO INTEGER NOT NULL,
  PRIMARY KEY(NUS_NUMERO),
  FOREIGN KEY(NUS_ORP_NUMERO) REFERENCES ORDEM_PRODUCAO
);

CREATE TABLE ROTA_PRODUTO (
  RTP_CODIGO CHAR(6) NOT NULL,
  RTP_DESCRICAO VARCHAR(30)DEFAULT NULL,
  RTP_ORP_NUMERO INTEGER NOT NULL,
  PRIMARY KEY(RTP_CODIGO),
  FOREIGN KEY(RTP_ORP_NUMERO) REFERENCES ORDEM_PRODUCAO
);

 CREATE TABLE ROTA_ESTACAO (
  RTE_RTP_CODIGO CHAR(6) NOT NULL,
  RTE_EST_CODIGO_ANT CHAR(6) NOT NULL,
  RTE_EST_CODIGO_PRO CHAR(6) NOT NULL,
  RTE_SEQUENCIA INTEGER NOT NULL,
  PRIMARY KEY(RTE_RTP_CODIGO, RTE_EST_CODIGO_ANT, RTE_EST_CODIGO_PRO),
  FOREIGN KEY(RTE_EST_CODIGO_ANT) REFERENCES ESTACAO,
  FOREIGN KEY(RTE_EST_CODIGO_PRO) REFERENCES ESTACAO,
  FOREIGN KEY(RTE_RTP_CODIGO)REFERENCES ROTA_PRODUTO
);

CREATE TABLE TIPO_ERRO (
  TRR_CODIGO CHAR(6) NOT NULL,
  TRR_DESCRICAO VARCHAR(30)DEFAULT NULL,
  PRIMARY KEY(TRR_CODIGO)
);

CREATE TABLE PRODUCAO (
  PDC_ID INTEGER NOT NULL,
  PDC_NUS_NUMERO VARCHAR(30) NOT NULL,
  PDC_EST_CODIGO CHAR(6) NOT NULL,
  PDC_TRR_CODIGO CHAR(6) NOT NULL,
  PDC_DATA DATE DEFAULT NULL,
  PDC_HORA TIME DEFAULT NULL,
  PRIMARY KEY(PDC_ID),
  FOREIGN KEY(PDC_TRR_CODIGO) REFERENCES TIPO_ERRO,
  FOREIGN KEY(PDC_EST_CODIGO) REFERENCES ESTACAO,
  FOREIGN KEY(PDC_NUS_NUMERO) REFERENCES NUM_SERIE
);


CREATE SEQUENCE pdc_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
COMMENT ON SEQUENCE pdc_id_seq IS 'Sequencia utilizada para o campo pdc_id na relacao producao.';





/*Antes de executar os codigos abaixo, deve-se criar a linguagem plpgsql*/

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
  HANDLER plpgsql_call_handler
  VALIDATOR plpgsql_validator;

/*PROCEDURE*/
create or replace function criarNumeroSerie()
returns trigger as $serializacao$

declare
sequencia integer := '0' ;
numero_serie varchar(30);
 Begin

  Loop
   sequencia := sequencia +1;
   numero_serie := new.orp_numero|| sequencia ;

   insert into num_serie values(numero_serie,new.orp_numero);
   exit when sequencia >= new.orp_qtdprod;
  End Loop;
 Return NEW;
End;
$serializacao$Language 'plpgsql';


/*TRIGGER*/
create trigger serializacao 
after insert on ordem_producao
for each row execute procedure
criarNumeroSerie(); 

-- View visao_consulta_producao
Drop view visao_consulta_producao;
CREATE OR REPLACE VIEW visao_consulta_producao AS 
 SELECT DISTINCT rota_estacao.rte_est_codigo_pro AS estacao, rte_sequencia, ( SELECT ordem_producao.orp_qtdprod
           FROM ordem_producao
          WHERE ordem_producao.orp_numero = 111111) AS qtd_prod, count(p.*) AS count, (( SELECT ordem_producao.orp_qtdprod
           FROM ordem_producao
          WHERE ordem_producao.orp_numero = 111111)) - count(p.*) AS dif
   FROM rota_estacao 
   LEFT JOIN producao p ON rota_estacao.rte_est_codigo_pro = p.pdc_est_codigo
   LEFT JOIN num_serie ON p.pdc_nus_numero::text = num_serie.nus_numero::text
   LEFT JOIN ordem_producao ON num_serie.nus_orp_numero = ordem_producao.orp_numero
   WHERE rte_sequencia < (select max(rte_sequencia) from rota_estacao)	
   GROUP BY rota_estacao.rte_est_codigo_pro, rota_estacao.rte_sequencia;


/*
PROCEDURE 2
create or replace function poeZero(integer)
Returns varchar(30) as $$

declare
in_numero alias for $1;
out_numero varchar(6);
num_cast varchar(6);
Begin
  num_cast := cast(in_numero) as varchar(6)
  Loop
    
   exit when sequencia >= new.orp_qtdprod;
  End Loop;
 Return NEW;
End;
$serializacao$Language 'plpgsql';
*/
